package com.xueqi.dao.impl;

import com.sun.scenario.effect.impl.sw.sse.SSEBlend_SRC_OUTPeer;
import com.xueqi.bean.EbookCategory;
import com.xueqi.bean.EbookEntry;
import com.xueqi.dao.EbookEntryDao;
import com.xueqi.utils.DbUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class EbookEntryDaoImpl extends DbUtils implements EbookEntryDao {

    @Override
    public List<EbookEntry> findall(int typeid,int pageindex,int pagesize) {
        List<EbookEntry> list=new ArrayList<>();
        //String sql="select book.*,tye.id tid,tye.name tyename FROM ebook_category tye,ebook_entry book WHERE tye.id=book.categoryId order by id desc";
        //动态sql
        StringBuffer  sql= new StringBuffer("select book.*,tye.id tid,tye.name tyename FROM ebook_category tye,ebook_entry book WHERE tye.id=book.categoryId ");
        if(typeid!=0){
            sql.append(" and categoryId=? ");//前后一定要加空格
        }
        sql.append(" order by id desc limit ?,? ");
        ArrayList params = new ArrayList();
        if(typeid!=0){
            params.add(typeid);
        }
        params.add((pageindex-1)*pagesize);
        params.add(pagesize);
        System.out.println();
        ResultSet resultSet=query(sql.toString(),params);
        try {
            while (resultSet.next()) {
                EbookEntry ebookEntry = new EbookEntry();
                ebookEntry.setId(resultSet.getInt("id"));
                ebookEntry.setTitle(resultSet.getString("title"));
                ebookEntry.setSummary(resultSet.getString("summary"));
                ebookEntry.setUploaduser(resultSet.getString("uploaduser"));
                ebookEntry.setCreatedate(resultSet.getDate("createdate"));
                EbookCategory category = new EbookCategory();
                category.setId(resultSet.getInt("tid"));
                category.setName(resultSet.getString("tyename"));
                ebookEntry.setType(category);
                list.add(ebookEntry);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            closeall();
        }
        return list;
    }

    @Override
    public int addBook(EbookEntry entry) {
       //1.定义sql
        int len=0;
        try {
            String sql="insert into ebook_entry values(null,?,?,?,?,?)";
            ArrayList list = new ArrayList();
            list.add(entry.getCategoryid());
            list.add(entry.getTitle());
            list.add(entry.getSummary());
            list.add(entry.getUploaduser());
            list.add(entry.getCreatedate());
            len = update(sql, list);

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            closeall();
        }
        return len;
    }

    @Override
    public EbookEntry findbyid(int id) {
        EbookEntry ebookEntry = new EbookEntry();
        try {
            String sql="select * from ebook_entry where id=?";
            List list=new ArrayList();
            list.add(id);
            ResultSet resultSet = query(sql, list);
            while (resultSet.next()) {
                ebookEntry.setId(resultSet.getInt("id"));
                ebookEntry.setTitle(resultSet.getString("title"));
                ebookEntry.setSummary(resultSet.getString("summary"));
                ebookEntry.setUploaduser(resultSet.getString("uploaduser"));
                ebookEntry.setCreatedate(resultSet.getDate("createdate"));
                ebookEntry.setCategoryid(resultSet.getInt("categoryId"));


            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeall();
        }
        return ebookEntry;
    }

    @Override
    public int UpdateBook(EbookEntry entry) {
        int update = 0;
        try {
            String sql="update ebook_entry set title=?,summary=?,uploaduser=?,createdate=?,categoryid=? where id=?";
            List params=new ArrayList();
            params.add(entry.getTitle());
            params.add(entry.getSummary());
            params.add(entry.getUploaduser());
            params.add(entry.getCreatedate());
            params.add(entry.getCategoryid());
            params.add(entry.getId());
            update = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeall();
        }
        return update;
    }

    @Override
    public int DeleteBook(int tid) {
        int i=0;
        try {
            String sql="delete from ebook_entry where id=?";
            ArrayList list = new ArrayList();
            list.add(tid);
            i = update(sql, list);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeall();
        }
        return i;
    }

    @Override
    public int totalcount(int typeid) {
        int count=0;
        try {
            StringBuffer sql=new StringBuffer("select count(*) from ebook_entry where 1=1");
            ArrayList params = new ArrayList();
            if(typeid!=0){
                sql.append(" and categoryid=? ");
                params.add(typeid);
            }
            ResultSet query = query(sql.toString(), params);
            while(query.next()){
                //count=query.getInt("count(*)");
                count=query.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeall();
        }
        return count;
    }

}
